package com.example.redislearning.mapper;

import com.example.redislearning.entity.User;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

/**
 * 用户数据访问层
 * MyBatis Mapper接口
 */
@Mapper
public interface UserMapper {

    /**
     * 插入用户
     */
    @Insert("INSERT INTO users (username, email, password, nickname, avatar, phone, status, points, total_study_time) " +
            "VALUES (#{username}, #{email}, #{password}, #{nickname}, #{avatar}, #{phone}, #{status}, #{points}, #{totalStudyTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);

    /**
     * 根据ID查询用户
     */
    @Select("SELECT * FROM users WHERE id = #{id} AND status = 1")
    User selectById(Integer id);

    /**
     * 根据用户名查询用户
     */
    @Select("SELECT * FROM users WHERE username = #{username} AND status = 1")
    User selectByUsername(String username);

    /**
     * 根据邮箱查询用户
     */
    @Select("SELECT * FROM users WHERE email = #{email} AND status = 1")
    User selectByEmail(String email);

    /**
     * 更新用户信息
     */
    @Update("UPDATE users SET nickname = #{nickname}, avatar = #{avatar}, phone = #{phone}, " +
            "points = #{points}, total_study_time = #{totalStudyTime}, updated_at = NOW() " +
            "WHERE id = #{id}")
    int updateById(User user);

    /**
     * 更新用户积分
     */
    @Update("UPDATE users SET points = points + #{points}, updated_at = NOW() WHERE id = #{userId}")
    int updatePoints(@Param("userId") Integer userId, @Param("points") Integer points);

    /**
     * 更新学习时长
     */
    @Update("UPDATE users SET total_study_time = total_study_time + #{studyTime}, updated_at = NOW() WHERE id = #{userId}")
    int updateStudyTime(@Param("userId") Integer userId, @Param("studyTime") Integer studyTime);

    /**
     * 分页查询用户列表
     */
    @Select("SELECT * FROM users WHERE status = 1 ORDER BY created_at DESC LIMIT #{offset}, #{limit}")
    List<User> selectUserList(@Param("offset") int offset, @Param("limit") int limit);

    /**
     * 查询用户总数
     */
    @Select("SELECT COUNT(*) FROM users WHERE status = 1")
    int countUsers();

    /**
     * 根据ID列表查询用户
     */
//    @Select({
//        "<script>",
//        "SELECT * FROM users WHERE id IN ",
//        "<foreach collection='userIds' item='id' open='(' separator=',' close=')'>",
//        "#{id}",
//        "</foreach>",
//        "AND status = 1",
//        "</script>"
//    })
    List<User> selectByIds(@Param("userIds") List<Integer> userIds);

    /**
     * 根据用户名统计数量
     */
    @Select("SELECT COUNT(*) FROM users WHERE username = #{username}")
    int countByUsername(String username);

    /**
     * 根据邮箱统计数量
     */
    @Select("SELECT COUNT(*) FROM users WHERE email = #{email}")
    int countByEmail(String email);

    /**
     * 更新用户积分（增量更新）
     */
    @Update("UPDATE users SET points = points + #{points}, updated_at = NOW() WHERE id = #{userId}")
    int incrementPoints(@Param("userId") Integer userId, @Param("points") Integer points);

    /**
     * 更新学习时长（增量更新）
     */
    @Update("UPDATE users SET total_study_time = total_study_time + #{duration}, updated_at = NOW() WHERE id = #{userId}")
    int incrementStudyDuration(@Param("userId") Integer userId, @Param("duration") Integer duration);

    /**
     * 更新最后登录时间
     */
    @Update("UPDATE users SET last_login_time = #{lastLoginTime}, updated_at = NOW() WHERE id = #{userId}")
    int updateLastLoginTime(@Param("userId") Integer userId, @Param("lastLoginTime") LocalDateTime lastLoginTime);

    /**
     * 删除用户（软删除）
     */
    @Update("UPDATE users SET status = 0, updated_at = NOW() WHERE id = #{userId}")
    int delete(Integer userId);

    /**
     * 获取积分排行榜
     */
    @Select("SELECT id as userId, username, points as score FROM users WHERE status = 1 ORDER BY points DESC LIMIT #{limit}")
    List<User> getPointsLeaderboard(@Param("limit") int limit);

    /**
     * 获取学习时长排行榜
     */
    @Select("SELECT id as userId, username, total_study_time as score FROM users WHERE status = 1 ORDER BY total_study_time DESC LIMIT #{limit}")
    List<User> getStudyDurationLeaderboard(@Param("limit") int limit);

    /**
     * 获取完成课程数排行榜
     */
    @Select("SELECT u.id as userId, u.username, COUNT(lc.id) as score " +
            "FROM users u LEFT JOIN learning_courses lc ON u.id = lc.user_id AND lc.status = 1 " +
            "WHERE u.status = 1 GROUP BY u.id, u.username ORDER BY score DESC LIMIT #{limit}")
    List<User> getCompletedCoursesLeaderboard(@Param("limit") int limit);

    /**
     * 获取用户学习统计
     */
    @Select({
        "SELECT ",
        "COUNT(DISTINCT c.id) as totalCourses,",
        "SUM(c.duration) as totalDuration,",
        "COUNT(DISTINCT CASE WHEN lc.status = 1 THEN c.id END) as completedCourses",
        "FROM users u",
        "LEFT JOIN learning_courses lc ON u.id = lc.user_id",
        "LEFT JOIN courses c ON lc.course_id = c.id",
        "WHERE u.id = #{userId} AND u.status = 1"
    })
    Map<String, Object> getLearningStats(Integer userId);
}